﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DataAccess
{
    public class GiaoVien
    {
        // các thuộc tính giáo viên:
        
        private string _MaGV;
        public string MaGV
        {
            get { return _MaGV; }
            set { _MaGV = value; }
        }

        private string _TenGV;
        public string TenGV
        {
            get { return _TenGV; }
            set { _TenGV = value; }
        }

        private string _ChucVu;
        public string ChucVu
        {
            get { return _ChucVu; }
            set { _ChucVu = value; }
        }

        private string _HocVi;

        public string HocVi
        {
            get { return _HocVi; }
            set { _HocVi = value; }
        }

        private string _Email;

        public string Email
        {
            get { return _Email; }
            set { _Email = value; }
        }

        private string _Telephone;

        public string Telephone
        {
            get { return _Telephone; }
            set { _Telephone = value; }
        }
        // các phương thức khởi tạo:
        public GiaoVien() { }

        public GiaoVien(string ma, string ten, string cv,string hv,string em,string phone)
        {
            MaGV = ma;
            TenGV = ten;
            ChucVu = cv;
            HocVi = hv;
            Email = em;
            Telephone = phone;
        }

        public GiaoVien(GiaoVien gv)
        {
            MaGV = gv.MaGV;
            TenGV = gv.TenGV;
            ChucVu = gv.ChucVu;
            HocVi = gv.HocVi;
            Email = gv.Email;
            Telephone = gv.Telephone;
        }
        // các phương thức khai thác đối tượng

        // xem tất cả giáo viên
        public static List<GiaoVien> All()
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_All", connec);
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }
                }
            }
            return list; 
             
        }
        //thêm giáo viên
        public static int Add(GiaoVien gv)
        {
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_Add",connec);
                cmd.Parameters.Add("@MaGV", SqlDbType.VarChar,10).Value = gv.MaGV;
                cmd.Parameters.Add("@TenGV", SqlDbType.NVarChar,50).Value =gv.TenGV ;
                cmd.Parameters.Add("@ChucVu", SqlDbType.NVarChar,50).Value = gv.ChucVu;
                cmd.Parameters.Add("@HocVi", SqlDbType.NVarChar, 50).Value = gv.HocVi;
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 200).Value = gv.Email;
                cmd.Parameters.Add("@telephone", SqlDbType.NVarChar, 20).Value = gv.Telephone;
                connec.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
            
        }

        // hàm xóa giáo viên
        public static int Delete(string MaGV)
        {
            try
            {
                using (SqlConnection connec = MyConnection.MyConn())
                {
                    SqlCommand cmd = MyCommand.Command("GiaoVien_Delete",connec);
                    cmd.Parameters.Add("@MaGV", SqlDbType.VarChar, 10).Value = MaGV;
                    connec.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0;
            }

        }

        // hàm thay đổi thông tin giáo viên:
        public static int Update(GiaoVien gv)
        {
            try
            {
                using (SqlConnection connec = MyConnection.MyConn())
                {
                    SqlCommand cmd = MyCommand.Command("GiaoVien_CapNhat", connec);

                    cmd.Parameters.Add("@MaGV", SqlDbType.VarChar, 10).Value = gv.MaGV;
                    cmd.Parameters.Add("@TenGV", SqlDbType.VarChar, 50).Value = gv.TenGV;
                    cmd.Parameters.Add("@ChucVu", SqlDbType.VarChar, 50).Value = gv.ChucVu;
                    cmd.Parameters.Add("@HocVi", SqlDbType.NVarChar, 50).Value = gv.HocVi;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 200).Value = gv.Email;
                    cmd.Parameters.Add("@telephone", SqlDbType.NVarChar, 20).Value = gv.Telephone;
                    connec.Open();
                    int after = cmd.ExecuteNonQuery();
                    return after;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0; ;
            }
            
        }

        public static GiaoVien Find_ByName(string name)
        {
            GiaoVien gv = new GiaoVien();
            using (SqlConnection connec=MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindByName", connec);
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
                connec.Open();
                using (SqlDataReader reader=cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                         gv = GetGV(reader);
                    }
                }
                return gv;
            }
        }

        public static List<GiaoVien> FindGiangVienOrChuyenVien()
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec= MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindGiangVienOrChuyenVien", connec);
                connec.Open();
                using (SqlDataReader reader=cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }
                    
                }
                return list;
            }
        }

        public static List<GiaoVien> FindByMaGV(string ma)
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindByMaGV", connec);
                cmd.Parameters.Add("@MaGV", SqlDbType.VarChar, 10).Value = ma;
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }

                }
                return list;
            }
        }

        public static List<GiaoVien> FindByTenGV(string ten)
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindByNameGV", connec);
                cmd.Parameters.Add("@TenGV", SqlDbType.NVarChar, 50).Value = ten;
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }

                }
                return list;
            }
        }

        public static List<GiaoVien> FindByCVGV(string cv)
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindByCVGV", connec);
                cmd.Parameters.Add("@CVGV", SqlDbType.NVarChar, 50).Value = cv;
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }

                }
                return list;
            }
        }

        public static List<GiaoVien> FindByHVGV(string hv)
        {
            List<GiaoVien> list = new List<GiaoVien>();
            using (SqlConnection connec = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("GiaoVien_FindByHVGV", connec);
                cmd.Parameters.Add("@HVGV", SqlDbType.NVarChar, 50).Value = hv;
                connec.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        GiaoVien gv = GetGV(reader);
                        list.Add(gv);
                    }

                }
                return list;
            }
        }
        // ham bo trợ:
        private static GiaoVien GetGV(SqlDataReader reader)
        {
            GiaoVien gv = new GiaoVien();
            gv.MaGV = reader["MaGV"].ToString();
            gv.TenGV = reader["TenGV"].ToString();
            gv.ChucVu = reader["ChucVu"].ToString();
            gv.HocVi = reader["HocVi"].ToString();
            gv.Email = reader["Email"].ToString();
            gv.Telephone = reader["Telephone"].ToString();
            return gv;
        }
    }
}
